OpenEdge Development:<br />Progress 4GL Reference PRESELECT phrase

PRESELECT [ EACH \| FIRST \| LAST ] `record-phrase` [ , [ EACH \| FIRST \| LAST ] `record-phrase` ] ... [ [ BREAK ] { BY `expression` [ DESCENDING ] \| COLLATE ( `string` , `strength` [ , `collation` ] ) [ DESCENDING ] } ... ]

PRESELECT
  [ EACH | FIRST | LAST ] record-phrase
  [ , [ EACH | FIRST | LAST ] record-phrase ] ...
  [ [ BREAK ]
       { BY expression [ DESCENDING ]
          | COLLATE ( string , strength [ , collation ] ) [ DESCENDING ]
       } ...
  ]

Goes through a table, selecting records that meet the criteria you specify in record-phrase. PRESELECT creates a temporary index that contains pointers to each of the preselected records in the database table. Then you can use other statements, such as FIND NEXT, within the block to process those records.

The record-phrase option identifies the criteria to use when preselecting records. Following is the syntax for the record-phrase:

{ `record` [ `field-list` ] } [ `constant` ] [ [ LEFT ] OUTER-JOIN ] [ OF `table` ] [ WHERE `expression` ] [ USE-INDEX `index` ] [ USING [ FRAME `frame` ] `field` [ AND [ FRAME `frame` ] `field` ] ... ] [ SHARE-LOCK \| EXCLUSIVE-LOCK \| NO-LOCK ] [ NO-PREFETCH ]

{ record [ field-list ] }
[ constant ]
[ [ LEFT ] OUTER-JOIN ] 
[ OF table ]
[ WHERE expression ]
[ USE-INDEX index ] 
[ USING [ FRAME frame ] field
    [ AND [ FRAME frame ] field ] ...
]
[ SHARE-LOCK | EXCLUSIVE-LOCK | NO-LOCK ]
[ NO-PREFETCH ]

Specifying multiple occurrences of record-phrase preselects the tables using an inner join. Also, any sorting you specify applies to all the tables. If you then do a FIND on the last table in the PRESELECT list, Progress reads records into the buffers for all of the tables in the list.

For more information on record-phrase and inner joins, see the Record phrase reference entry.

When used in combination with the FIRST function, LAST function, FIRST-OF function, and LAST-OF function, BREAK indicates that subgroups are used for aggregation. If you use BREAK, you must also use BY.

Sorts the preselected records by the value of expression. If you do not use the BY option, PRESELECT sorts the records in order by the index used to extract the records. The DESCENDING option sorts the records in descending order (not in the default ascending order).

Generates the collation value of a string after applying a particular strength, and optionally, a particular collation. The DESCENDING option sorts the records in descending order (not in default ascending order).

A CHARACTER expression that evaluates to the string whose collation value you want to generate.

A CHARACTER expression that evaluates to a Progress comparison strength or an International Components for Unicode (ICU) comparison strength.

RAW — Generates a collation value for the string based on its binary value.

CASE-SENSITIVE — Generates a case-sensitive collation value for the string based on a particular collation. If you specify this strength with an ICU collation, Progress applies the ICU TERTIARY strength.

CASE-INSENSITIVE — Generates a case-insensitive collation value for the string based on a particular collation. If you specify this strength with an ICU collation, Progress applies the ICU SECONDARY strength.

CAPS — Generates a collation value for the string based on its binary value after converting any lowercase letters in the string to uppercase letters, based on the settings of the Internal Code Page (-cpinternal) and Case Table (-cpcase) startup parameters.

PRIMARY — Generates a collation value for the base characters in the string.

SECONDARY — Generates a collation value for the base characters and any diacritical marks in the string.

TERTIARY — Generates a case-sensitive collation value for the base characters and any diacritical marks in the string.

QUATERNARY — Generates a case-sensitive collation value for the base characters and any diacritical marks in the string, and distinguishes words with and without punctuation. ICU uses this strength to distinguish between Hiragana and Katakana when applied with the ICU-JA (Japanese) collation. Otherwise, it is the same as TERTIARY.

IGNORE-SECONDARY — Generates a case-sensitive, but diacritically-insensitive, collation value for the string.

A CHARACTER expression that evaluates to the name of a Progress collation table or ICU collation. If collation does not appear, COLLATE uses the collation table of the client.

strength does not evaluate to a valid value.

collation does not evaluate to a collation table residing in the convmap.cp file.

collation evaluates to a collation table that is not defined for the code page corresponding to the -cpinternal startup parameter.

To process a multi-table collection gathered by the PRESELECT option, use the last table named in the collection when you want to read the selected records. Progress then automatically retrieves records from the other tables.

r-presl1.p
REPEAT PRESELECT EACH order, customer OF order, EACH order-line OF order BY order.order-date BY order.cust-num BY order-line.item-num: FIND NEXT order-line. DISPLAY order.order-date order.cust-num customer.name order-line.item-num. END.

r-presl1.p

REPEAT PRESELECT EACH order, customer OF order, EACH order-line OF order
            BY order.order-date BY order.cust-num BY order-line.item-num:
   FIND NEXT order-line.
   DISPLAY order.order-date order.cust-num customer.name
           order-line.item-num.
END.

The PRESELECT option in this example selects the logically joined record that consists of order, order-line, and customer, and makes all of these records available in the REPEAT block. Usually you perform more complex processing within the PRESELECT block.

If, within a PRESELECT block, you find a record using the ROWID of that record, Progress disregards any other selection criteria you applied to the PRESELECT. For example, suppose the ROWID of order number 4 is stored in the variable ord-rowid:

DO PRESELECT EACH order WHERE order-num > 5: FIND FIRST order WHERE ROWID(order) = ord-rowid. DISPLAY order. END.

In this example, Progress finds and displays order number 4 even though the selection criteria specifies that the order number must be greater than 5. The ROWID always overrides other selection criteria. Furthermore, if you use FIND...WHERE ROWID(record) =..., the index cursor is not reset in the preselected list. That is, even if record ROWID(record) is in the preselected list, FIND NEXT does not find the record that follows it in the preselected list.

PRESELECT phrase

Syntax

See also